package com.yss.sofa.report.engine.util;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.FormulaError;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.yss.sofa.report.engine.Report;
import com.yss.sofa.report.engine.cell.page.PageCell;
import com.yss.sofa.report.engine.cell.visible.result.ResultCell;

public final class Excel2003Util {
	private Excel2003Util(){}
	private static XSSFWorkbook toExcel2007(Report report) throws Exception{
		ByteArrayInputStream in=new ByteArrayInputStream(report.getMiddle());
		try {
			XSSFWorkbook excel=new XSSFWorkbook(in);
			return excel;
		} finally {
			in.close();
		}
	}
	public static byte[] toExcel2003(Report report) throws Exception{
		ByteArrayOutputStream out = new ByteArrayOutputStream();
		try {
			export(report, out);
			out.flush();
			return out.toByteArray();
		} finally {
			out.close();
		}
	}
	public static void export(Report report, String filename) throws Exception {
		FileOutputStream out = new FileOutputStream(new File(filename));
		try {
			export(report, out);
			out.flush();
		} finally {
			if(out!=null){
				out.close();
			}
		}
	}
	private static short convertColorFrom2007To2003(XSSFColor color2007,HSSFPalette palette2003,short none){
		if(color2007==null){
			return none;
		}
		byte[] rgb=color2007.getRGBWithTint();
		if(rgb==null || rgb.length<3){
			return none;
		}
		HSSFColor color2003 = palette2003.findSimilarColor(rgb[0],rgb[1],rgb[2]);
		if(color2003==null){
			color2003=palette2003.addColor(rgb[0],rgb[1],rgb[2]);
		}
		return color2003.getIndex();
	}
	private static void convertFontFrom2007To2003(XSSFFont font2007,HSSFFont font2003, HSSFPalette palette2003){
		font2003.setBold(font2007.getBold());
		font2003.setCharSet(font2007.getCharSet());
		font2003.setColor(convertColorFrom2007To2003(font2007.getXSSFColor(),palette2003,(short)0));
		font2003.setFontHeight(font2007.getFontHeight());
		font2003.setFontHeightInPoints(font2007.getFontHeightInPoints());
		font2003.setFontName(font2007.getFontName());
		font2003.setItalic(font2007.getItalic());
		font2003.setStrikeout(font2007.getStrikeout());
		font2003.setTypeOffset(font2007.getTypeOffset());
		font2003.setUnderline(font2007.getUnderline());
	}
	private static void convertStyleFrom2007To2003(XSSFCellStyle style2007,HSSFCellStyle style2003, HSSFPalette palette2003, HSSFDataFormat format2003){
		style2003.setAlignment(style2007.getAlignmentEnum());
		style2003.setBorderBottom(style2007.getBorderBottomEnum());
		style2003.setBorderLeft(style2007.getBorderLeftEnum());
		style2003.setBorderRight(style2007.getBorderRightEnum());
		style2003.setBorderTop(style2007.getBorderTopEnum());
		style2003.setBottomBorderColor(convertColorFrom2007To2003(style2007.getBottomBorderXSSFColor(), palette2003, (short)0));
		
		style2003.setDataFormat(format2003.getFormat((style2007.getDataFormatString())));
		style2003.setFillBackgroundColor(convertColorFrom2007To2003(style2007.getFillBackgroundXSSFColor(), palette2003, (short)0));
		style2003.setFillForegroundColor(convertColorFrom2007To2003(style2007.getFillForegroundXSSFColor(), palette2003, (short)0));
		style2003.setFillPattern(style2007.getFillPatternEnum());
		style2003.setHidden(style2007.getHidden());
		style2003.setIndention(style2007.getIndention());
		style2003.setLeftBorderColor(convertColorFrom2007To2003(style2007.getLeftBorderXSSFColor(), palette2003, (short)0));
		style2003.setLocked(style2007.getLocked());
		//style2003.setReadingOrder(); 2007 is none
		style2003.setRightBorderColor(convertColorFrom2007To2003(style2007.getRightBorderXSSFColor(), palette2003, (short)0));
		style2003.setRotation(style2007.getRotation());
		style2003.setShrinkToFit(style2007.getShrinkToFit());
		style2003.setTopBorderColor(convertColorFrom2007To2003(style2007.getTopBorderXSSFColor(), palette2003, (short)0));
		//style2003.setUserStyleName(); not
		style2003.setVerticalAlignment(style2007.getVerticalAlignmentEnum());
		style2003.setWrapText(style2007.getWrapText());
	}
	private static XSSFCellStyle getExcel2007CellStyle(XSSFSheet sheet2007,int row,int col){
		if(sheet2007==null){
			return null;
		}
		XSSFRow row2007=sheet2007.getRow(row);
		if(row2007==null){
			return null;
		}
		XSSFCell cell2007=row2007.getCell(col);
		if(cell2007==null){
			return null;
		}
		XSSFCellStyle style2007=cell2007.getCellStyle();
		return style2007;
	}
	public static void export(Report report, OutputStream out) throws Exception {
		HSSFWorkbook excel2003=new HSSFWorkbook();
		XSSFWorkbook excel2007=toExcel2007(report);
		String name=report.getReportName();
		Set<String> froms=report.getSheetMapping().keySet();
		XSSFFormulaEvaluator evaluator = excel2007.getCreationHelper().createFormulaEvaluator();
		try {
			//default font
			excel2003.getFontAt((short)0).setBold(excel2007.getFontAt((short)0).getBold());
			excel2003.getFontAt((short)0).setCharSet(excel2007.getFontAt((short)0).getCharSet());
			excel2003.getFontAt((short)0).setFontHeight(excel2007.getFontAt((short)0).getFontHeight());
			excel2003.getFontAt((short)0).setFontHeightInPoints(excel2007.getFontAt((short)0).getFontHeightInPoints());
			excel2003.getFontAt((short)0).setFontName(excel2007.getFontAt((short)0).getFontName());
			excel2003.getFontAt((short)0).setItalic(excel2007.getFontAt((short)0).getItalic());
			excel2003.getFontAt((short)0).setStrikeout(excel2007.getFontAt((short)0).getStrikeout());
			excel2003.getFontAt((short)0).setTypeOffset(excel2007.getFontAt((short)0).getTypeOffset());
			excel2003.getFontAt((short)0).setUnderline(excel2007.getFontAt((short)0).getUnderline());
			//style & font & color
			HSSFDataFormat format2003=excel2003.createDataFormat();
			HSSFPalette palette2003=excel2003.getCustomPalette();
			CreationHelper helper2003 = excel2003.getCreationHelper();
			Map<String, HSSFCellStyle> style2003s=new HashMap<String, HSSFCellStyle>();
			
			List<XSSFFont> hSSFFontList= new ArrayList<XSSFFont>();
			List<XSSFCellStyle> xSSFCellStyleList= new ArrayList<XSSFCellStyle>();
			
			Map<XSSFFont, HSSFFont> fontMap=new HashMap<XSSFFont, HSSFFont>();
			Map<XSSFCellStyle, HSSFCellStyle> styleMap=new HashMap<XSSFCellStyle, HSSFCellStyle>();
			for(String from:froms){
				String to=report.getSheetMapping().get(from);
				int row=report.getRowCount(to);
				int col=report.getColCount(to);
				if((row>=65536) || (col>=256)){
					throw new Exception("报表["+name+"]的行列数超过了excel2003的最大限制");
				}
				XSSFSheet template=excel2007.getSheet(from);
				for(int r=0;r<row;r++){
					for(int c=0;c<col;c++){
						ResultCell cell=report.getCell(to, r, c);
						if(cell!=null){
							XSSFCellStyle style2007=getExcel2007CellStyle(template, cell.getStyleRfrom(), cell.getStyleCfrom());
							String style=to+"_"+cell.getStyleRfrom()+"_"+cell.getStyleCfrom();
							if (null == style2007) {
								continue;
							}
							if (!isExistXSSFCellStyle(xSSFCellStyleList, style2007)) {
								xSSFCellStyleList.add(style2007);
								if (!style2003s.containsKey(style)) {
									HSSFFont font2003 = null;
									if (!existFont(style2007.getFont(), hSSFFontList)) {
										font2003 = excel2003.findFont(style2007.getFont().getBold(), style2007.getFont().getColor(),style2007.getFont().getFontHeight(), style2007.getFont().getFontName(),
															   style2007.getFont().getItalic(), style2007.getFont().getStrikeout(),
															   style2007.getFont().getTypeOffset(), style2007.getFont().getUnderline());
										if (font2003 == null) {
											font2003 = excel2003.createFont();
											convertFontFrom2007To2003(style2007.getFont(), font2003, palette2003);
											hSSFFontList.add(style2007.getFont());
											fontMap.put(style2007.getFont(), font2003);
										}
									} else {
										font2003 = fontMap.get(style2007.getFont());
									}

									HSSFCellStyle style2003 = excel2003.createCellStyle();
									style2003.setFont(font2003);
									convertStyleFrom2007To2003(style2007, style2003, palette2003, format2003);
									styleMap.put(style2007, style2003);
									style2003s.put(style, style2003);
								}
							} else {
								style2003s.put(style, styleMap.get(style2007));
							}
						}
					}
				}
			}
			//sheets
			for(String from:froms){
				String to=report.getSheetMapping().get(from);
				int row=report.getRowCount(to);
				int col=report.getColCount(to);
				if((row>=65536) || (col>=256)){
					throw new Exception("报表["+name+"]的行列数超过了excel2003的最大限制");
				}
				excel2003.createSheet(to);
			}
			for(String from:froms){
				String to=report.getSheetMapping().get(from);
				PageCell page = report.getPage(to);
				int row=report.getRowCount(to);
				int col=report.getColCount(to);
				XSSFSheet sheet2007=excel2007.getSheet(to);
				HSSFSheet sheet2003=excel2003.getSheet(to);
				//value
				for(int r=0;r<row;r++){
					XSSFRow row2007=sheet2007.getRow(r);
					HSSFRow row2003=sheet2003.createRow(r);
					for(int c=0;c<col;c++){
						ResultCell cell=report.getCell(to, r, c);
						String style=null;
						if(cell!=null){
							style=to+"_"+cell.getStyleRfrom()+"_"+cell.getStyleCfrom();
						}
						XSSFCell cell2007=row2007.getCell(c);
						HSSFCell cell2003=row2003.createCell(c);
						if(cell2007!=null){
							cell2003.setCellType(cell2007.getCellTypeEnum());
							switch (cell2007.getCellTypeEnum()) {
								case BLANK:
									break;
								case BOOLEAN:
									cell2003.setCellValue(cell2007.getBooleanCellValue());
									break;
								case ERROR:
									cell2003.setCellErrorValue(FormulaError.forInt(cell2007.getErrorCellValue()));
									break;
								case FORMULA:
									if(!page.isIncludeFormula()){
										CellValue cv=evaluator.evaluate(cell2007);
										switch (cv.getCellTypeEnum()) {
											case BLANK:
												break;
											case BOOLEAN:
												cell2003.setCellValue(cv.getBooleanValue());
												break;
											case ERROR:
												cell2003.setCellValue(cv.formatAsString());
												break;
											case NUMERIC:
												cell2003.setCellValue(cv.getNumberValue());
												break;
											case STRING:
												cell2003.setCellValue(cv.getStringValue());
												break;
											default:
												cell2003.setCellValue(cv.formatAsString());
												break;
										}
									}else{
										cell2003.setCellFormula(cell2007.getCellFormula());
									}
									break;
								case NUMERIC:
									cell2003.setCellValue(cell2007.getNumericCellValue());
									break;
								case STRING:
									cell2003.setCellValue(cell2007.getStringCellValue());
									break;
								default:
									cell2003.setCellValue(cell2007.getStringCellValue());
									break;
							}
						} else {
							cell2003.setCellType(CellType.BLANK);
						}
						//String value=getExcel2007CellFormattedValue(cell,cell2007,evaluator2007);
						//cell2003.setCellType(CellType.STRING);
						//cell2003.setCellValue(value);
						if(style!=null){
							HSSFCellStyle style2003=style2003s.get(style);
							cell2003.setCellStyle(style2003);
						}
					}
				}
				//merge
				Set<ResultCell> mergeCells=report.getRanges(to);
				if(!mergeCells.isEmpty()){
					for(ResultCell cell:mergeCells){
						int top=cell.getRbegin();
						int left=cell.getCbegin();
						int bottom=cell.getRend();
						int right=cell.getCend();
						sheet2003.addMergedRegion(new CellRangeAddress(top, bottom, left, right));
					}
				}
				//size
				for(int r=0;r<row;r++){
					sheet2003.getRow(r).setHeight(report.isRowAutoSize(to,r)?(short)-1:sheet2007.getRow(r).getHeight());
				}
				for(int c=0;c<col;c++){
					sheet2003.setColumnWidth(c, sheet2007.getColumnWidth(c));
				}
				//image
				HSSFPatriarch drawing2003 = sheet2003.createDrawingPatriarch();
				Map<ResultCell,byte[]> images=report.getImages(to);
				for(ResultCell key:images.keySet()){
					byte[] png=images.get(key);
					int id=excel2003.addPicture(png, HSSFWorkbook.PICTURE_TYPE_PNG);
					ClientAnchor anchor = helper2003.createClientAnchor();
					anchor.setCol1(key.getCbegin());
					anchor.setCol2(key.getCend()+1);
					anchor.setRow1(key.getRbegin());
					anchor.setRow2(key.getRend()+1);
					drawing2003.createPicture(anchor, id);
				}
				//page
				sheet2003.getPrintSetup().setLandscape(sheet2007.getPrintSetup().getLandscape());
				sheet2003.getPrintSetup().setPaperSize(sheet2007.getPrintSetup().getPaperSize());
				sheet2003.setMargin(HSSFSheet.TopMargin, sheet2007.getMargin(XSSFSheet.TopMargin));
				sheet2003.setMargin(HSSFSheet.BottomMargin, sheet2007.getMargin(XSSFSheet.BottomMargin));
				sheet2003.setMargin(HSSFSheet.LeftMargin, sheet2007.getMargin(XSSFSheet.LeftMargin));
				sheet2003.setMargin(HSSFSheet.RightMargin, sheet2007.getMargin(XSSFSheet.RightMargin));
				sheet2003.setMargin(HSSFSheet.HeaderMargin, sheet2007.getMargin(XSSFSheet.HeaderMargin));
				sheet2003.setMargin(HSSFSheet.FooterMargin, sheet2007.getMargin(XSSFSheet.FooterMargin));
				//header & footer
				sheet2003.getHeader().setLeft(sheet2007.getHeader().getLeft());
				sheet2003.getHeader().setCenter(sheet2007.getHeader().getCenter());
				sheet2003.getHeader().setRight(sheet2007.getHeader().getRight());
				sheet2003.getFooter().setLeft(sheet2007.getFooter().getLeft());
				sheet2003.getFooter().setCenter(sheet2007.getFooter().getCenter());
				sheet2003.getFooter().setRight(sheet2007.getFooter().getRight());
				//out
				if(report.getPage(to).getTitleCount()+report.getPage(to).getHeaderCount()>0){
					sheet2003.createFreezePane(0, report.getPage(to).getTitleCount()+report.getPage(to).getHeaderCount());
				}
				sheet2003.setDisplayGridlines(false);
				sheet2003.setForceFormulaRecalculation(true);
			}
			style2003s.clear();
			excel2003.setForceFormulaRecalculation(true);
			excel2003.setFirstVisibleTab(0);
			excel2003.write(out);
		} finally {
			excel2003.close();
		}
	}
	
	private static boolean isExistXSSFCellStyle(List<XSSFCellStyle> xSSFCellStyleList, XSSFCellStyle style2007) {
		boolean isExist = false;
		for (XSSFCellStyle xSSFCellStyle : xSSFCellStyleList) {
			if (xSSFCellStyle.equals(style2007)) {
				isExist = true;
				break;
			}
		}
		return isExist;
	}
	
	private static boolean existFont(XSSFFont font2003, List<XSSFFont> hSSFFontList) {

		boolean isExist = false;
		for (XSSFFont hSSFFont : hSSFFontList) {
			if (hSSFFont.equals(font2003)) {
				isExist = true;
				break;
			}
		}
		return isExist;
	}
}
